package com.settlement.system.service.impl;

import cn.hutool.core.lang.Validator;
import com.settlement.system.common.exception.ServiceException;
import com.settlement.system.common.util.common.*;
import com.settlement.system.mapper.BaseMapper;
import com.settlement.system.mapper.TblQueryFieldConfigMapper;
import com.settlement.system.mapper.TblQueryJoinTableMapper;
import com.settlement.system.mapper.TblQueryTableConfigMapper;
import com.settlement.system.model.entity.common.TblQueryFieldConfig;
import com.settlement.system.model.entity.common.TblQueryJoinTable;
import com.settlement.system.model.vo.ConditionVo;
import com.settlement.system.model.vo.TableVo;
import com.settlement.system.service.ITblQueryFieldConfigService;
import com.settlement.system.service.ITblQueryJoinTableService;
import com.settlement.system.service.ITblRrBillTypeService;
import jakarta.annotation.Resource;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.ResultHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 联表查询主表 服务实现类
 *
 * @author xiaochuan
 * @since 2022-01-13
 */
@Slf4j
@Service
public class TblQueryJoinTableServiceImpl extends BaseServiceImpl implements ITblQueryJoinTableService {

    private static final Pattern COMPILE = Pattern.compile("[\\s]+[\\u4e00-\\u9fa5\\-_]{1,}[\\s]+");

    private static final Pattern COMPILE_COLUMN = Pattern.compile("[\\u4e00-\\u9fa5\\-_]{1,}\\.[\\u4e00-\\u9fa5\\-A-Za-z_0-9【】；，。（）\\()\\/]{1,}");

    private static final Pattern COMPILE_SUM_COLUMN = Pattern.compile("[\\u4e00-\\u9fa5\\-_]{1,}\\.{2}[\\u4e00-\\u9fa5\\-A-Za-z_0-9【】；，。（）\\()\\/]{1,}");

    private static final int PATTERN_FLAGS = Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL;
    private static final Pattern COMPILE_SELECT = Pattern.compile("(\\S*\\s+SELECT\\s+\\.*)|(^SELECT\\.*)", PATTERN_FLAGS);
    private static final Pattern COMPILE_UPDATE = Pattern.compile("(\\S*\\s+UPDATE\\s+\\.*)|(^UPDATE\\.*)", PATTERN_FLAGS);
    private static final Pattern COMPILE_DELETE = Pattern.compile("(\\S*\\s+DELETE\\s+\\.*)|(^DELETE\\.*)", PATTERN_FLAGS);
    private static final Pattern COMPILE_INSERT = Pattern.compile("(\\S*\\s+INSERT\\s+\\.*)|(^INSERT\\.*)", PATTERN_FLAGS);

    @Resource
    private TblQueryJoinTableMapper tblQueryJoinTableMapper;

    @Resource
    private TblQueryTableConfigMapper tblQueryTableConfigMapper;

    @Resource
    private TblQueryFieldConfigMapper tblQueryFieldConfigMapper;

    @Resource
    private ITblQueryFieldConfigService tblQueryFieldConfigService;

    @Resource
    private ITblRrBillTypeService tblRrBillTypeService;

    @Override
    public BaseMapper init() {
        return tblQueryJoinTableMapper;
    }

    @Override
    public String getDataBaseTableNameByTableNameCn(String tableNameCn) {
        return tblQueryTableConfigMapper.getDataBaseTableNameByTableNameCn(tableNameCn);
    }

    @Override
    public TblQueryFieldConfig getDataBaseColumnNameByModel(TblQueryFieldConfig tblIifConditionConfig) {
        return tblQueryFieldConfigMapper.getDataBaseColumnNameByModel(tblIifConditionConfig);
    }

    @SneakyThrows
    @Override
    public String appendCondition(TblQueryJoinTable model) {
        String tableJson = model.getTableJson();
        List<TableVo> tableVos = JsonUtil.readListValue(tableJson, TableVo.class);
        StringBuilder sqlBuilder = StrUtil.appendBuilder(new StringBuilder("SELECT " + model.getSelectedFields() + " FROM "), model.getTableName() + " ");
        for (TableVo tableVo : tableVos) {
            StrUtil.appendBuilder(sqlBuilder, "LEFT JOIN ", tableVo.getTableName(), " ON ", tableVo.getMasterColumnName(), "=", tableVo.getSavleColumnName() + " ");
        }
        String conditionJson = model.getConditionJson();
        List<ConditionVo> conditionVos = JsonUtil.readListValue(conditionJson, ConditionVo.class);
        StrUtil.appendBuilder(sqlBuilder, "WHERE ");
        for (ConditionVo conditionVo : conditionVos) {
            if (StringUtils.isNotBlank(conditionVo.getKey())) {
                StrUtil.appendBuilder(sqlBuilder, conditionVo.getKey().replace("@和@", "AND").replace("@或@", "OR"), " ", conditionVo.getConditionKey(), " ", conditionVo.getConditionValue() + " ");
            } else {
                StrUtil.appendBuilder(sqlBuilder, conditionVo.getConditionKey(), " ", conditionVo.getConditionValue() + " ");
            }
        }
        //加上默认的  主表.ID=?
        //StrUtil.appendBuilder(sqlBuilder," AND ",model.getTableName(),".ID=?");
        //处理替换特需字段
        String replace = sqlBuilder.toString().replace("  ", " ");
        replace = replace.replace("不包含", "NOT IN").replace("包含", "IN").replace("不类似", "NOT LIKE").replace("类似", "LIKE");
        //replace+=" and rownum <" + (model.getSelectCount()+1);
        model.setCondition(replace.trim());
        return replace;
    }

    @SneakyThrows
    @Override
    public String appendGroupCondition(TblQueryJoinTable model) {
        String summaryFields = model.getSummaryFields();
        if (StringUtils.isEmpty(summaryFields)) {
            model.setGroupCondition("");
            return null;
        }
        String tableJson = model.getTableJson();
        List<TableVo> tableVos = JsonUtil.readListValue(tableJson, TableVo.class);
        StringBuilder groupBySqlBuilder = new StringBuilder();

        StrUtil.appendBuilder(groupBySqlBuilder, "SELECT " + model.getGroupFields() + " ");

        String[] fieldArr = summaryFields.split(",");
        for (String field : fieldArr) {
            String replace = field.replace(".", "..");
            StrUtil.appendBuilder(groupBySqlBuilder, ", SUM( " + field + " ) " + replace);
        }
        StrUtil.appendBuilder(groupBySqlBuilder, " FROM " + model.getTableName() + " ");

        for (TableVo tableVo : tableVos) {
            StrUtil.appendBuilder(groupBySqlBuilder, "LEFT JOIN ", tableVo.getTableName(), " ON ", tableVo.getMasterColumnName(), "=", tableVo.getSavleColumnName() + " ");
        }
        String conditionJson = model.getConditionJson();
        List<ConditionVo> conditionVos = JsonUtil.readListValue(conditionJson, ConditionVo.class);
        StrUtil.appendBuilder(groupBySqlBuilder, "WHERE ");
        for (ConditionVo conditionVo : conditionVos) {
            if (StringUtils.isNotBlank(conditionVo.getKey())) {
                StrUtil.appendBuilder(groupBySqlBuilder, conditionVo.getKey().replace("@和@", "AND").replace("@或@", "OR"), " ", conditionVo.getConditionKey(), " ", conditionVo.getConditionValue() + " ");
            } else {
                StrUtil.appendBuilder(groupBySqlBuilder, conditionVo.getConditionKey(), " ", conditionVo.getConditionValue() + " ");
            }
        }
        StrUtil.appendBuilder(groupBySqlBuilder, " GROUP BY ", model.getGroupFields() + " ");
        //处理替换特需字段
        String replace = groupBySqlBuilder.toString().replace("  ", " ");
        replace = replace.replace("不包含", "NOT IN").replace("包含", "IN").replace("不类似", "NOT LIKE").replace("类似", "LIKE");
        model.setGroupCondition(replace.trim());
        return replace;
    }


    @Override
    public void sqlMatcher(TblQueryJoinTable model) {
        model.setSuccess(false);
        model.setErrorMsg("匹配失败");
        String condition = QueryJoinTableUtil.decode(model.getCondition());
        try {
            StringBuffer sqlStrBuffer = new StringBuffer();
            StringBuffer sqlBuffer = new StringBuffer();
            //匹配中文表名
            Matcher matcher = COMPILE.matcher(condition);
            while (matcher.find()) {
                String tableName = StringUtils.trim(matcher.group());
                String tableNameZn = this.getDataBaseTableNameByTableNameCn(tableName);
                if (StringUtils.isNotBlank(tableNameZn)) {
                    model.setSuccess(true);
                    //解析字符串，查询表映射替换成英文字段，然后替换，替换掉查找到的字符串
                    matcher.appendReplacement(sqlBuffer, " " + tableNameZn + " ");
                } else {
                    model.setSuccess(false);
                    model.setErrorMsg("匹配表名 " + tableName + " 失败");
                    break;
                }
            }

            if (!model.isSuccess()) {
                return;
            }

            matcher.appendTail(sqlBuffer);
            //开始匹配中文字段
            Matcher matcherColumn = COMPILE_COLUMN.matcher(sqlBuffer.toString());
            TblQueryFieldConfig tblQueryFieldConfig;
            while (matcherColumn.find()) {
                String columnName = matcherColumn.group();
                if (StringUtils.indexOf(columnName, ".") == -1) {
                    model.setSuccess(false);
                    model.setErrorMsg("匹配列 " + columnName + " 失败");
                    break;
                }
                String[] strArr = StringUtils.split(columnName, ".");
                {
                    tblQueryFieldConfig = new TblQueryFieldConfig();
                    tblQueryFieldConfig.setTableNameCn(strArr[0]);
                    tblQueryFieldConfig.setColumnNameCn(strArr[1]);
                    TblQueryFieldConfig conditionConfig = tblQueryFieldConfigService.getByTableNameCnAndColumnNameCn(tblQueryFieldConfig);
                    if (conditionConfig != null) {
                        String columnStr = new StringBuffer().append(conditionConfig.getTableNameZn()).append(".").append(conditionConfig.getColumnNameZn()).toString();
                        //解析字符串，查询表映射替换成英文字段，然后替换，替换掉查找到的字符串
                        matcherColumn.appendReplacement(sqlStrBuffer, columnStr);
                    } else {
                        log.error("条件配置表里面的表名：{},字段：{},不存在！", strArr[0], strArr[1]);
                        model.setSuccess(false);
                        model.setErrorMsg("匹配字段 " + strArr[0] + "" + strArr[1] + " 失败");
                        break;
                    }
                }
            }

            if (!model.isSuccess()) {
                return;
            }

            matcherColumn.appendTail(sqlStrBuffer);
            String sqlStr = sqlStrBuffer.toString();
            sqlStr = QueryJoinTableUtil.convertAccountingPeriodToOutDate(sqlStr);

            String returnStr = sqlStr;
            if (model.getSelectCount() != null) {
                returnStr = sqlStr + " and rownum <" + (model.getSelectCount() + 1);
            }

            String exeSqlStr = "select * from (" + sqlStr + ") where rownum = 1";

            model.setSqlStr(sqlStr);
            model.setReturnStr(returnStr);
            if (model.isIfCount()) {
                String totalCountStr = "select count(1) from (" + sqlStr + ")";
                model.setTotalCount(getCountBySql(totalCountStr));
                model.setSuccess(true);
            } else {
                //校验是否是查询语句，才去执行
                boolean matchRegex = Validator.isMatchRegex("(SELECT||select)(.+)", sqlStr);
                if (matchRegex) {
                    executeSqlStr(exeSqlStr.trim());
                    model.setSuccess(true);
                }
            }

        } catch (Exception e) {
            log.error("条件匹配生成sql异常{}", e.getMessage());
            if (e.getCause() != null) {
                model.setErrorMsg(e.getCause().getMessage());
            }
            model.setSuccess(false);
        }
        return;
    }

    @Override
    public void groupSqlMatcher(TblQueryJoinTable model) {
        String condition = QueryJoinTableUtil.decode(model.getGroupCondition());
        if (StringUtils.isEmpty(condition)) {
            model.setGroupSqlStr("");
            return;
        }

        model.setSuccess(false);
        model.setErrorMsg("匹配失败");
        try {
            StringBuffer sqlStrBuffer = new StringBuffer();
            StringBuffer groupSqlStrBuffer = new StringBuffer();
            StringBuffer sqlBuffer = new StringBuffer();
            //匹配中文表名
            Matcher matcher = COMPILE.matcher(condition);
            while (matcher.find()) {
                String tableName = StringUtils.trim(matcher.group());
                String tableNameZn = this.getDataBaseTableNameByTableNameCn(tableName);
                if (StringUtils.isNotBlank(tableNameZn)) {
                    model.setSuccess(true);
                    //解析字符串，查询表映射替换成英文字段，然后替换，替换掉查找到的字符串
                    matcher.appendReplacement(sqlBuffer, " " + tableNameZn + " ");
                } else {
                    model.setSuccess(false);
                    model.setErrorMsg("匹配表名 " + tableName + " 失败");
                    break;
                }
            }

            if (!model.isSuccess()) {
                return;
            }

            matcher.appendTail(sqlBuffer);


            //开始匹配中文字段
            Matcher matcherColumn = COMPILE_COLUMN.matcher(sqlBuffer.toString());
            TblQueryFieldConfig tblQueryFieldConfig;
            while (matcherColumn.find()) {
                String columnName = matcherColumn.group();
                if (StringUtils.indexOf(columnName, ".") == -1) {
                    model.setSuccess(false);
                    model.setErrorMsg("匹配列 " + columnName + " 失败");
                    break;
                }
                String[] strArr = StringUtils.split(columnName, ".");
                {
                    tblQueryFieldConfig = new TblQueryFieldConfig();
                    tblQueryFieldConfig.setTableNameCn(strArr[0]);
                    tblQueryFieldConfig.setColumnNameCn(strArr[1]);
                    TblQueryFieldConfig conditionConfig = tblQueryFieldConfigService.getByTableNameCnAndColumnNameCn(tblQueryFieldConfig);
                    if (conditionConfig != null) {
                        String columnStr = new StringBuffer().append(conditionConfig.getTableNameZn()).append(".").append(conditionConfig.getColumnNameZn()).toString();
                        //解析字符串，查询表映射替换成英文字段，然后替换，替换掉查找到的字符串
                        matcherColumn.appendReplacement(sqlStrBuffer, columnStr);
                    } else {
                        log.error("条件配置表里面的表名：{},字段：{},不存在！", strArr[0], strArr[1]);
                        model.setSuccess(false);
                        model.setErrorMsg("匹配字段 " + strArr[0] + "" + strArr[1] + " 失败");
                        break;
                    }
                }
            }

            if (!model.isSuccess()) {
                return;
            }

            matcherColumn.appendTail(sqlStrBuffer);


            //开始匹配汇总字段别名
            Matcher summaryMatcherColumn = COMPILE_SUM_COLUMN.matcher(sqlStrBuffer.toString());
            while (summaryMatcherColumn.find()) {
                String columnName = summaryMatcherColumn.group();
                if (StringUtils.indexOf(columnName, ".") == -1) {
                    model.setSuccess(false);
                    model.setErrorMsg("匹配列 " + columnName + " 失败");
                    break;
                }
                String[] strArr = StringUtils.split(columnName, ".");
                {
                    tblQueryFieldConfig = new TblQueryFieldConfig();
                    tblQueryFieldConfig.setTableNameCn(strArr[0]);
                    tblQueryFieldConfig.setColumnNameCn(strArr[1]);
                    TblQueryFieldConfig conditionConfig = tblQueryFieldConfigService.getByTableNameCnAndColumnNameCn(tblQueryFieldConfig);
                    if (conditionConfig != null) {
                        String columnStr = new StringBuffer().append(conditionConfig.getColumnNameZn()).toString();
                        //解析字符串，查询表映射替换成英文字段，然后替换，替换掉查找到的字符串
                        summaryMatcherColumn.appendReplacement(groupSqlStrBuffer, columnStr);
                    } else {
                        log.error("条件配置表里面的表名：{},字段：{},不存在！", strArr[0], strArr[1]);
                        model.setSuccess(false);
                        model.setErrorMsg("匹配字段 " + strArr[0] + "" + strArr[1] + " 失败");
                        break;
                    }
                }
            }

            if (!model.isSuccess()) {
                return;
            }

            summaryMatcherColumn.appendTail(groupSqlStrBuffer);

            String groupSqlStr = groupSqlStrBuffer.toString();
            groupSqlStr = QueryJoinTableUtil.convertAccountingPeriodToOutDate(groupSqlStr);
            model.setGroupSqlStr(groupSqlStr);

            //校验是否是查询语句
            boolean matchRegex = Validator.isMatchRegex("(SELECT||select)(.+)", groupSqlStr);
            if (matchRegex) {
                executeSqlStr(groupSqlStr.trim());
                model.setSuccess(true);
            }
        } catch (Exception e) {
            log.error("条件匹配生成sql异常{}", e.getMessage());
            if (e.getCause() != null) {
                model.setErrorMsg(e.getCause().getMessage());
            }
            if ("ORA-01722: 无效数字\n".equals(model.getErrorMsg())) {
                model.setErrorMsg("汇总字段的类型必须是数字（数量或金额）");
            }
            model.setSuccess(false);
        }
        return;
    }

    @Override
    public Integer getCountBySql(String sql) throws Exception {
        List<Object> objects = executeSqlStr(sql);
        if (!objects.isEmpty()) {
            return Integer.parseInt(objects.get(0).toString());
        }
        return null;
    }

    @Override
    public List<Object> executeSqlStr(String sql) throws Exception {
        try {
            boolean hasUpdate = false;
            boolean hasDelete = false;
            boolean hasInsert = false;
            boolean hasSelect = false;
            Matcher matcher = COMPILE_UPDATE.matcher(sql);
            boolean b = matcher.find();
            if (b) {
                String group = matcher.group();
                log.info("exist update,{}", group);
                hasUpdate = true;
            }

            matcher = COMPILE_DELETE.matcher(sql);
            b = matcher.find();
            if (b) {
                String group = matcher.group();
                log.info("exist delete,{}", group);
                hasDelete = true;
            }

            matcher = COMPILE_INSERT.matcher(sql);
            b = matcher.find();
            if (b) {
                String group = matcher.group();
                log.info("exist insert,{}", group);
                hasInsert = true;
            }

            matcher = COMPILE_SELECT.matcher(sql);
            b = matcher.find();
            if (b) {
                String group = matcher.group();
                log.info("exist select,{}", group);
                hasSelect = true;
            }


            log.info("executeSqlStr,{}", sql);
            if (!hasSelect ||
                    hasUpdate ||
                    hasDelete ||
                    hasInsert) {
                log.info("只能是查询语句,{}", sql);
                throw new ServiceException("只能是查询语句");
            }
            return tblQueryJoinTableMapper.executeSqlStr(sql);
        } catch (Exception ex) {
            log.error("executeSqlStr error", ex);
            throw ex;
        }
    }

    @Override
    public <ModelType> void getDataByHandler(Map<String, Object> params, ResultHandler<ModelType> handler) throws ServiceException {
        try {
            String type = params.get("type") + "";
            if ("origin".equalsIgnoreCase(type)) {
                super.getDataByHandler(params, handler);
            } else if ("extend".equalsIgnoreCase(type)) {
                tblQueryJoinTableMapper.getDataByHandler(params, (ResultHandler<Map<String, Object>>) handler);
            }
        } catch (Exception ex) {
            log.error("流式导出异常", ex);
            throw new ServiceException(ex);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public String checkBeforeCopy(List<TblQueryJoinTable> copyList) throws ServiceException {
        try {
            if (CollectionUtils.isEmpty(copyList)) {
                return "没有选中的数据,不能执行复制操作";
            }

            for (TblQueryJoinTable info : copyList) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", info.getId());
                List<TblQueryJoinTable> objects = tblQueryJoinTableMapper.selectByParam(map);
                if (!CollectionUtils.isEmpty(objects)) {
                    TblQueryJoinTable tblQueryJoinTable = objects.get(0);
                    tblQueryJoinTable.setId(null);
                    tblQueryJoinTable.setQueryName(tblQueryJoinTable.getQueryName() + "（" + DateUtil.format(new Date(), "YYYY-MM-dd HH-mm-ss") + "）");
                    SystemUserUtil.setDefualt(tblQueryJoinTable);
                    tblQueryJoinTableMapper.insert(tblQueryJoinTable);
                }
            }
        } catch (Exception ex) {
            throw new ServiceException("刷新异常", ex);
        }
        return null;
    }

    @Override
    public ITblRrBillTypeService getTblRrBillTypeService() {
        return tblRrBillTypeService;
    }

    public static void main(String[] args) throws Exception {
        String str = "delete" +
                "TBL_TEST" +
                "SET" +
                "AGE=1" +
                "WHERE SELECT = 1";
        List<Object> objects = new TblQueryJoinTableServiceImpl().executeSqlStr(str);
        System.out.println(objects);

    }
}

